﻿using ChoWorkflowServer.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ChoWorkflowServer.ModelSqlite
{
    internal class ChoDataModelSql
    {
        public static readonly string TASKS_TABLE_NAME = "CHO_TASKS";
        public static readonly string TASK_GRPS_TABLE_NAME = "CHO_TASK_GRPS";
        public static readonly string TASK_SCHEDULES_TABLE_NAME = "CHO_TASK_SCHEDULES";
        public static readonly string TASK_BATCH_TABLE_NAME = "CHO_TASK_BATCH";
        public static readonly string TASK_STATUS_TABLE_NAME = "CHO_TASK_STATUS";
        public static readonly string HOLIDAY_CALENDAR_TABLE_NAME = "CHO_HOLIDAY_CALENDAR";
        public static readonly string SCHEDULE_TYPES_TABLE_NAME = "CHO_SCHEDULE_TYPES";
        public static readonly string HOLIDAY_CALENDAR_DATES_TABLE_NAME = "CHO_HOLIDAY_CALENDAR_DATES";
        public static readonly string USERS_TABLE_NAME = "CHO_USERS";
        public static readonly string USER_ROLES_TABLE_NAME = "CHO_USER_ROLES";
        public static readonly string ROLE_POLICY_TABLE_NAME = "CHO_ROLE_POLICY";
        public static readonly string POLICY_TABLE_NAME = "CHO_POLICY";
        public static readonly string ROLES_TABLE_NAME = "CHO_ROLES";

        public static readonly string GET_MAX_TASK_ID = "SELECT MAX(TASK_ID) FROM {0}".FormatString(TASKS_TABLE_NAME);
        public static readonly string GET_MAX_TASK_GRP_ID = "SELECT MAX(TASK_GRP_ID) FROM {0}".FormatString(TASK_GRPS_TABLE_NAME);
        public static readonly string GET_MAX_TASK_SCHEDULE_ID = "SELECT MAX(SCHEDULE_ID) FROM {0}".FormatString(TASK_SCHEDULES_TABLE_NAME);
        public static readonly string GET_MAX_HOLIDAY_CAL_ID = "SELECT MAX(CAL_ID) FROM {0}".FormatString(HOLIDAY_CALENDAR_TABLE_NAME);
        public static readonly string GET_MAX_USER_ID = "SELECT MAX(ID) FROM {0}".FormatString(USERS_TABLE_NAME);
        public static readonly string GET_MAX_ROLE_ID = "SELECT MAX(ID) FROM {0}".FormatString(ROLES_TABLE_NAME);

        public static readonly string GET_TASK_GRP_ID = "SELECT TASK_GRP_ID FROM {0} WHERE TASK_GRP_NAME = @TASK_GRP_NAME".FormatString(TASK_GRPS_TABLE_NAME);

        public static readonly string IS_TASK_EXISTS = "SELECT 1 FROM {0} WHERE TASK_NAME = @TASK_NAME".FormatString(TASKS_TABLE_NAME);
        public static readonly string GET_TASK_ID = "SELECT TASK_ID FROM {0} WHERE TASK_NAME = @TASK_NAME".FormatString(TASKS_TABLE_NAME);

        public static readonly string UPDATE_TASK = @"UPDATE {0}
            SET 
                TASK_NAME = @TASK_NAME, 
                WORKFLOW_NAME = @WORKFLOW_NAME, 
                TASK_DESC = @TASK_DESC, 
                TASK_GRP_ID = @TASK_GRP_ID, 
                CMD_PARAMETERS = @CMD_PARAMETERS, 
                NO_OF_RETRY = @NO_OF_RETRY, 
                SLEEP_BETWEEN_RETRY = @SLEEP_BETWEEN_RETRY, 
                TIMEOUT = @TIMEOUT, 
                MACHINE_NAME = @MACHINE_NAME, 
                MODIFIED_BY = @MODIFIED_BY,
                MODIFIED_DATE = @MODIFIED_DATE
            WHERE
                TASK_ID = @TASK_ID".FormatString(TASKS_TABLE_NAME);
        public static readonly string INSERT_TASK = @"INSERT INTO {0} 
            (TASK_ID, TASK_NAME, WORKFLOW_NAME, TASK_DESC, TASK_GRP_ID, CMD_PARAMETERS, NO_OF_RETRY, SLEEP_BETWEEN_RETRY, TIMEOUT, 
                MACHINE_NAME, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)
            VALUES (@TASK_ID, @TASK_NAME, @WORKFLOW_NAME, @TASK_DESC, @TASK_GRP_ID, @CMD_PARAMETERS, @NO_OF_RETRY, @SLEEP_BETWEEN_RETRY, 
                @TIMEOUT, @MACHINE_NAME, @MODIFIED_BY, @MODIFIED_DATE, @MODIFIED_BY, @MODIFIED_DATE)".FormatString(TASKS_TABLE_NAME);
        public static readonly string DELETE_TASK = @"DELETE FROM {0} 
			WHERE TASK_NAME = @TASK_NAME".FormatString(TASKS_TABLE_NAME);

        public static readonly string IS_TASK_EXISTS_IN_GROUP = @"SELECT 1 FROM {0} WHERE TASK_NAME = @TASK_NAME 
            AND TASK_GRP_ID = @TASK_GRP_ID".FormatString(TASKS_TABLE_NAME);
        public static readonly string MOVE_TASK = @"UPDATE {0} SET TASK_GRP_ID = @TASK_GRP_ID, 
            MODIFIED_BY = @MODIFIED_BY, MODIFIED_DATE = @MODIFIED_DATE WHERE TAKS_NAME = @TASK_NAME".FormatString(TASKS_TABLE_NAME);

        public static readonly string GET_ALL_TASKS_GRPS = @"SELECT
            TASK_GRP_ID,
            TASK_GRP_NAME,
            TASK_GRP_DESC,
            PARENT,
            CREATED_BY,
            CREATED_DATE,
            MODIFIED_BY,
            MODIFIED_DATE
        FROM 
            {0}".FormatString(TASK_GRPS_TABLE_NAME);

        public static readonly string GET_ALL_TASKS_UI1 = @"SELECT
			T.TASK_NAME,
			T.WORKFLOW_NAME,
			IFNULL(TS.SCHEDULE_NAME, '') as SCHEDULE_NAME,
			IFNULL(B.BATCH_ID, 0) as BATCH_ID,
			IFNULL(B.TASK_STATUS, 1) as TASK_STATUS,
			IFNULL(S.TASK_STATUS_DESC, 'INACTIVE') as TASK_STATUS_DESC,
			T.CMD_PARAMETERS,
			B.CMD_PARAMETERS as BATCH_CMD_PARAMETERS,
			T.TASK_DESC,
			T.TASK_ID,
			TS.SCHEDULE_ID,
			G.TASK_GRP_ID,
			G.PARENT,
			G.TASK_GRP_NAME,
			G.TASK_GRP_DESC,
			B.LOG_MSG,
			B.START_TIME,
			B.END_TIME,
			B.DURATION,
			B.NEXT_RUN_TIME,
			B.EXIT_CODE,
			T.NO_OF_RETRY,
			T.SLEEP_BETWEEN_RETRY,
			T.[TIMEOUT],
			B.RETRY_COUNT,
			T.MACHINE_NAME,
			B.MACHINE_NAME as BATCH_MACHINE_NAME,
			B.MODIFIED_BY,
			IFNULL(B.MODIFIED_DATE, '1/1/1900') as MODIFIED_DATE,
			T.CREATED_DATE as TASK_CREATED_DATE,
			T.CREATED_BY as TASK_CREATED_BY,
			T.MODIFIED_DATE as TASK_MODIFIED_DATE,
			T.MODIFIED_BY as TASK_MODIFIED_BY,
			G.CREATED_DATE as TASK_GRP_CREATED_DATE,
			G.CREATED_BY as TASK_GRP_CREATED_BY,
			G.MODIFIED_DATE as TASK_GRP_MODIFIED_DATE,
			G.MODIFIED_BY as TASK_GRP_MODIFIED_BY
		FROM
            {0} G			
			LEFT OUTER JOIN {1} T ON T.TASK_GRP_ID = G.TASK_GRP_ID 
			LEFT OUTER JOIN {2} TS ON TS.TASK_ID = T.TASK_ID
			LEFT OUTER JOIN {3} B ON T.TASK_ID = B.TASK_ID AND TS.SCHEDULE_ID = B.SCHEDULE_ID
			LEFT OUTER JOIN {4} S ON B.TASK_STATUS = S.TASK_STATUS_ID
			".FormatString(TASK_GRPS_TABLE_NAME, TASKS_TABLE_NAME, TASK_SCHEDULES_TABLE_NAME, TASK_BATCH_TABLE_NAME, TASK_STATUS_TABLE_NAME);
        public static readonly string GET_ALL_TASKS_UI2 = @"SELECT 
			T.TASK_NAME,
			T.WORKFLOW_NAME,
			IFNULL(TS.SCHEDULE_NAME, '') as SCHEDULE_NAME,
			IFNULL(B.BATCH_ID, 0) as BATCH_ID,
			IFNULL(B.TASK_STATUS, 1) as TASK_STATUS,
			IFNULL(S.TASK_STATUS_DESC, 'INACTIVE') as TASK_STATUS_DESC,
			T.CMD_PARAMETERS,
			B.CMD_PARAMETERS as BATCH_CMD_PARAMETERS,
			T.TASK_DESC,
			T.TASK_ID,
			TS.SCHEDULE_ID,
			G.TASK_GRP_ID,
			G.PARENT,
			G.TASK_GRP_NAME,
			G.TASK_GRP_DESC,
			B.LOG_MSG,
			B.START_TIME,
			B.END_TIME,
			B.DURATION,
			B.NEXT_RUN_TIME,
			B.EXIT_CODE,
			T.NO_OF_RETRY,
			T.SLEEP_BETWEEN_RETRY,
			T.[TIMEOUT],
			B.RETRY_COUNT,
			T.MACHINE_NAME,
			B.MACHINE_NAME as BATCH_MACHINE_NAME,
			B.MODIFIED_BY,
			IFNULL(B.MODIFIED_DATE, '1/1/1900') as MODIFIED_DATE,
			T.CREATED_DATE as TASK_CREATED_DATE,
			T.CREATED_BY as TASK_CREATED_BY,
			T.MODIFIED_DATE as TASK_MODIFIED_DATE,
			T.MODIFIED_BY as TASK_MODIFIED_BY,
			G.CREATED_DATE as TASK_GRP_CREATED_DATE,
			G.CREATED_BY as TASK_GRP_CREATED_BY,
			G.MODIFIED_DATE as TASK_GRP_MODIFIED_DATE,
			G.MODIFIED_BY as TASK_GRP_MODIFIED_BY
		FROM
            {0} G			
			LEFT OUTER JOIN {1} T ON T.TASK_GRP_ID = G.TASK_GRP_ID AND T.TASK_NAME = @TASK_NAME
			LEFT OUTER JOIN {2} TS ON TS.TASK_ID = T.TASK_ID
			LEFT OUTER JOIN {3} B ON T.TASK_ID = B.TASK_ID AND TS.SCHEDULE_ID = B.SCHEDULE_ID
			LEFT OUTER JOIN {4} S ON B.TASK_STATUS = S.TASK_STATUS_ID
		WHERE 
            T.TASK_NAME = @TASK_NAME".FormatString(TASK_GRPS_TABLE_NAME, TASKS_TABLE_NAME, TASK_SCHEDULES_TABLE_NAME, TASK_BATCH_TABLE_NAME, TASK_STATUS_TABLE_NAME);
        public static readonly string GET_ALL_TASKS_UI3 = @"SELECT 
			T.TASK_NAME,
			T.WORKFLOW_NAME,
			IFNULL(TS.SCHEDULE_NAME, '') as SCHEDULE_NAME,
			IFNULL(B.BATCH_ID, 0) as BATCH_ID,
			IFNULL(B.TASK_STATUS, 1) as TASK_STATUS,
			IFNULL(S.TASK_STATUS_DESC, 'INACTIVE') as TASK_STATUS_DESC,
			T.CMD_PARAMETERS,
			B.CMD_PARAMETERS as BATCH_CMD_PARAMETERS,
			T.TASK_DESC,
			T.TASK_ID,
			TS.SCHEDULE_ID,
			G.TASK_GRP_ID,
			G.PARENT,
			G.TASK_GRP_NAME,
			G.TASK_GRP_DESC,
			B.LOG_MSG,
			B.START_TIME,
			B.END_TIME,
			B.DURATION,
			B.NEXT_RUN_TIME,
			B.EXIT_CODE,
			T.NO_OF_RETRY,
			T.SLEEP_BETWEEN_RETRY,
			T.[TIMEOUT],
			B.RETRY_COUNT,
			T.MACHINE_NAME,
			B.MACHINE_NAME as BATCH_MACHINE_NAME,
			B.MODIFIED_BY,
			IFNULL(B.MODIFIED_DATE, '1/1/1900') as MODIFIED_DATE,
			T.CREATED_DATE as TASK_CREATED_DATE,
			T.CREATED_BY as TASK_CREATED_BY,
			T.MODIFIED_DATE as TASK_MODIFIED_DATE,
			T.MODIFIED_BY as TASK_MODIFIED_BY,
			G.CREATED_DATE as TASK_GRP_CREATED_DATE,
			G.CREATED_BY as TASK_GRP_CREATED_BY,
			G.MODIFIED_DATE as TASK_GRP_MODIFIED_DATE,
			G.MODIFIED_BY as TASK_GRP_MODIFIED_BY
		FROM
            {0} G			
			LEFT OUTER JOIN {1} T ON T.TASK_GRP_ID = G.TASK_GRP_ID 
			LEFT OUTER JOIN {2} TS ON TS.TASK_ID = T.TASK_ID
			LEFT OUTER JOIN {3} B ON T.TASK_ID = B.TASK_ID AND TS.SCHEDULE_ID = B.SCHEDULE_ID
			LEFT OUTER JOIN {4} S ON B.TASK_STATUS = S.TASK_STATUS_ID
		WHERE 
			(G.TASK_GRP_NAME = @TASK_GRP_NAME OR
            G.TASK_GRP_NAME IN (SELECT B.TASK_GRP_NAME as CHILDREN
            FROM
	            CHO_TASK_GRPS A,
	            CHO_TASK_GRPS B
	            WHERE A.TASK_GRP_ID = B.PARENT 
	            AND A.TASK_GRP_NAME = @TASK_GRP_NAME))".FormatString(TASK_GRPS_TABLE_NAME, TASKS_TABLE_NAME, TASK_SCHEDULES_TABLE_NAME, TASK_BATCH_TABLE_NAME, TASK_STATUS_TABLE_NAME);
        public static readonly string GET_ALL_TASKS_UI4 = @"SELECT 
			T.TASK_NAME,
			T.WORKFLOW_NAME,
			IFNULL(TS.SCHEDULE_NAME, '') as SCHEDULE_NAME,
			IFNULL(B.BATCH_ID, 0) as BATCH_ID,
			IFNULL(B.TASK_STATUS, 1) as TASK_STATUS,
			IFNULL(S.TASK_STATUS_DESC, 'INACTIVE') as TASK_STATUS_DESC,
			T.CMD_PARAMETERS,
			B.CMD_PARAMETERS as BATCH_CMD_PARAMETERS,
			T.TASK_DESC,
			T.TASK_ID,
			TS.SCHEDULE_ID,
			G.TASK_GRP_ID,
			G.PARENT,
			G.TASK_GRP_NAME,
			G.TASK_GRP_DESC,
			B.LOG_MSG,
			B.START_TIME,
			B.END_TIME,
			B.DURATION,
			B.NEXT_RUN_TIME,
			B.EXIT_CODE,
			T.NO_OF_RETRY,
			T.SLEEP_BETWEEN_RETRY,
			T.[TIMEOUT],
			B.RETRY_COUNT,
			T.MACHINE_NAME,
			B.MACHINE_NAME as BATCH_MACHINE_NAME,
			B.MODIFIED_BY,
			IFNULL(B.MODIFIED_DATE, '1/1/1900') as MODIFIED_DATE,
			T.CREATED_DATE as TASK_CREATED_DATE,
			T.CREATED_BY as TASK_CREATED_BY,
			T.MODIFIED_DATE as TASK_MODIFIED_DATE,
			T.MODIFIED_BY as TASK_MODIFIED_BY,
			G.CREATED_DATE as TASK_GRP_CREATED_DATE,
			G.CREATED_BY as TASK_GRP_CREATED_BY,
			G.MODIFIED_DATE as TASK_GRP_MODIFIED_DATE,
			G.MODIFIED_BY as TASK_GRP_MODIFIED_BY
		FROM
            {0} G			
			LEFT OUTER JOIN {1} T ON T.TASK_GRP_ID = G.TASK_GRP_ID AND T.TASK_NAME = @TASK_NAME
			LEFT OUTER JOIN {2} TS ON TS.TASK_ID = T.TASK_ID
			LEFT OUTER JOIN {3} B ON T.TASK_ID = B.TASK_ID AND TS.SCHEDULE_ID = B.SCHEDULE_ID
			LEFT OUTER JOIN {4} S ON B.TASK_STATUS = S.TASK_STATUS_ID
		WHERE 
            T.TASK_NAME = @TASK_NAME
            AND G.TASK_GRP_NAME = @TASK_GRP_NAME".FormatString(TASK_GRPS_TABLE_NAME, TASKS_TABLE_NAME, TASK_SCHEDULES_TABLE_NAME, TASK_BATCH_TABLE_NAME, TASK_STATUS_TABLE_NAME);
        public static readonly string GET_ALL_TASKS1 = @"SELECT 
			T.TASK_NAME,
			T.WORKFLOW_NAME,
			IFNULL(TS.SCHEDULE_NAME, '') as SCHEDULE_NAME,
			IFNULL(B.BATCH_ID, 0) as BATCH_ID,
			IFNULL(B.TASK_STATUS, 1) as TASK_STATUS,
			IFNULL(S.TASK_STATUS_DESC, 'INACTIVE') as TASK_STATUS_DESC,
			T.CMD_PARAMETERS,
			B.CMD_PARAMETERS as BATCH_CMD_PARAMETERS,
			T.TASK_DESC,
			T.TASK_ID,
			TS.SCHEDULE_ID,
			G.TASK_GRP_ID,
			G.PARENT,
			G.TASK_GRP_NAME,
			G.TASK_GRP_DESC,
			B.LOG_MSG,
			B.START_TIME,
			B.END_TIME,
			B.DURATION,
			B.NEXT_RUN_TIME,
			B.EXIT_CODE,
			T.NO_OF_RETRY,
			T.SLEEP_BETWEEN_RETRY,
			T.[TIMEOUT],
			B.RETRY_COUNT,
			T.MACHINE_NAME,
			B.MACHINE_NAME as BATCH_MACHINE_NAME,
			B.MODIFIED_BY,
			IFNULL(B.MODIFIED_DATE, '1/1/1900') as MODIFIED_DATE,
			T.CREATED_DATE as TASK_CREATED_DATE,
			T.CREATED_BY as TASK_CREATED_BY,
			T.MODIFIED_DATE as TASK_MODIFIED_DATE,
			T.MODIFIED_BY as TASK_MODIFIED_BY,
			G.CREATED_DATE as TASK_GRP_CREATED_DATE,
			G.CREATED_BY as TASK_GRP_CREATED_BY,
			G.MODIFIED_DATE as TASK_GRP_MODIFIED_DATE,
			G.MODIFIED_BY as TASK_GRP_MODIFIED_BY
		FROM
			{1} T
			LEFT OUTER JOIN {0} G ON T.TASK_GRP_ID = G.TASK_GRP_ID
			LEFT OUTER JOIN {2} TS ON TS.TASK_ID = T.TASK_ID
			LEFT OUTER JOIN {3} B ON T.TASK_ID = B.TASK_ID AND TS.SCHEDULE_ID = B.SCHEDULE_ID
			LEFT OUTER JOIN {4} S ON B.TASK_STATUS = S.TASK_STATUS_ID
		WHERE 
            (B.MACHINE_NAME IS NULL OR B.MACHINE_NAME LIKE IFNULL(@MACHINE_NAME, '%'))
			AND (B.MODIFIED_DATE IS NOT NULL AND B.MODIFIED_DATE >= @MODIFIED_DATE)".FormatString(TASK_GRPS_TABLE_NAME, TASKS_TABLE_NAME, 
                                                                               TASK_SCHEDULES_TABLE_NAME, TASK_BATCH_TABLE_NAME, 
                                                                               TASK_STATUS_TABLE_NAME);
        public static readonly string GET_ALL_TASKS2 = @"SELECT 
			T.TASK_NAME,
			T.WORKFLOW_NAME,
			IFNULL(TS.SCHEDULE_NAME, '') as SCHEDULE_NAME,
			IFNULL(B.BATCH_ID, 0) as BATCH_ID,
			IFNULL(B.TASK_STATUS, 1) as TASK_STATUS,
			IFNULL(S.TASK_STATUS_DESC, 'INACTIVE') as TASK_STATUS_DESC,
			T.CMD_PARAMETERS,
			B.CMD_PARAMETERS as BATCH_CMD_PARAMETERS,
			T.TASK_DESC,
			T.TASK_ID, 
			TS.SCHEDULE_ID,
			G.TASK_GRP_ID,
			G.PARENT,
			G.TASK_GRP_NAME,
			G.TASK_GRP_DESC,
			B.LOG_MSG,
			B.START_TIME,
			B.END_TIME,
			B.DURATION,
			B.NEXT_RUN_TIME,
			B.EXIT_CODE,
			T.NO_OF_RETRY,
			T.SLEEP_BETWEEN_RETRY,
			T.[TIMEOUT],
			B.RETRY_COUNT,
			T.MACHINE_NAME,
			B.MACHINE_NAME as BATCH_MACHINE_NAME,
			B.MODIFIED_BY,
			IFNULL(B.MODIFIED_DATE, '1/1/1900') as MODIFIED_DATE,
			T.CREATED_DATE as TASK_CREATED_DATE,
			T.CREATED_BY as TASK_CREATED_BY,
			T.MODIFIED_DATE as TASK_MODIFIED_DATE,
			T.MODIFIED_BY as TASK_MODIFIED_BY,
			G.CREATED_DATE as TASK_GRP_CREATED_DATE,
			G.CREATED_BY as TASK_GRP_CREATED_BY,
			G.MODIFIED_DATE as TASK_GRP_MODIFIED_DATE,
			G.MODIFIED_BY as TASK_GRP_MODIFIED_BY
		FROM
			{1} T
			LEFT OUTER JOIN {0} G ON T.TASK_GRP_ID = G.TASK_GRP_ID
			LEFT OUTER JOIN {2} TS ON TS.TASK_ID = T.TASK_ID
			LEFT OUTER JOIN {3} B ON T.TASK_ID = B.TASK_ID AND TS.SCHEDULE_ID = B.SCHEDULE_ID
			LEFT OUTER JOIN {4} S ON B.TASK_STATUS = S.TASK_STATUS_ID
		WHERE 
			T.TASK_NAME = @TASK_NAME
            AND (B.MACHINE_NAME IS NULL OR B.MACHINE_NAME LIKE IFNULL(@MACHINE_NAME, '%'))
			AND (B.MODIFIED_DATE IS NOT NULL AND B.MODIFIED_DATE >= @MODIFIED_DATE)".FormatString(TASK_GRPS_TABLE_NAME, TASKS_TABLE_NAME, TASK_SCHEDULES_TABLE_NAME, TASK_BATCH_TABLE_NAME, TASK_STATUS_TABLE_NAME, ChoDbDateTime.MinValue.ToString("MM/dd/yyyy"));
        public static readonly string GET_ALL_TASKS3 = @"SELECT 
			T.TASK_NAME,
			T.WORKFLOW_NAME,
			IFNULL(TS.SCHEDULE_NAME, '') as SCHEDULE_NAME,
			IFNULL(B.BATCH_ID, 0) as BATCH_ID,
			IFNULL(B.TASK_STATUS, 1) as TASK_STATUS,
			IFNULL(S.TASK_STATUS_DESC, 'INACTIVE') as TASK_STATUS_DESC,
			T.CMD_PARAMETERS,
			B.CMD_PARAMETERS as BATCH_CMD_PARAMETERS,
			T.TASK_DESC,
			T.TASK_ID, 
			TS.SCHEDULE_ID,
			G.TASK_GRP_ID,
			G.PARENT,
			G.TASK_GRP_NAME,
			G.TASK_GRP_DESC,
			B.LOG_MSG,
			B.START_TIME,
			B.END_TIME,
			B.DURATION,
			B.NEXT_RUN_TIME,
			B.EXIT_CODE,
			T.NO_OF_RETRY,
			T.SLEEP_BETWEEN_RETRY,
			T.[TIMEOUT],
			B.RETRY_COUNT,
			T.MACHINE_NAME,
			B.MACHINE_NAME as BATCH_MACHINE_NAME,
			B.MODIFIED_BY,
			IFNULL(B.MODIFIED_DATE, '1/1/1900') as MODIFIED_DATE,
			T.CREATED_DATE as TASK_CREATED_DATE,
			T.CREATED_BY as TASK_CREATED_BY,
			T.MODIFIED_DATE as TASK_MODIFIED_DATE,
			T.MODIFIED_BY as TASK_MODIFIED_BY,
			G.CREATED_DATE as TASK_GRP_CREATED_DATE,
			G.CREATED_BY as TASK_GRP_CREATED_BY,
			G.MODIFIED_DATE as TASK_GRP_MODIFIED_DATE,
			G.MODIFIED_BY as TASK_GRP_MODIFIED_BY
		FROM
			{1} T
			LEFT OUTER JOIN {0} G ON T.TASK_GRP_ID = G.TASK_GRP_ID
			LEFT OUTER JOIN {2} TS ON TS.TASK_ID = T.TASK_ID
			LEFT OUTER JOIN {3} B ON T.TASK_ID = B.TASK_ID AND TS.SCHEDULE_ID = B.SCHEDULE_ID
			LEFT OUTER JOIN {4} S ON B.TASK_STATUS = S.TASK_STATUS_ID
		WHERE 
			(G.TASK_GRP_NAME = @TASK_GRP_NAME OR
            G.TASK_GRP_NAME IN (SELECT B.TASK_GRP_NAME as CHILDREN
            FROM
	            CHO_TASK_GRPS A,
	            CHO_TASK_GRPS B
	            WHERE A.TASK_GRP_ID = B.PARENT 
	            AND A.TASK_GRP_NAME = @TASK_GRP_NAME))
            AND (B.MACHINE_NAME IS NULL OR B.MACHINE_NAME LIKE IFNULL(@MACHINE_NAME, '%'))
			AND (B.MODIFIED_DATE IS NOT NULL AND B.MODIFIED_DATE >= @MODIFIED_DATE)".FormatString(TASK_GRPS_TABLE_NAME, TASKS_TABLE_NAME, TASK_SCHEDULES_TABLE_NAME, TASK_BATCH_TABLE_NAME, TASK_STATUS_TABLE_NAME, ChoDbDateTime.MinValue.ToString("MM/dd/yyyy"));
        public static readonly string GET_ALL_TASKS4 = @"SELECT 
			T.TASK_NAME,
			T.WORKFLOW_NAME,
			IFNULL(TS.SCHEDULE_NAME, '') as SCHEDULE_NAME,
			IFNULL(B.BATCH_ID, 0) as BATCH_ID,
			IFNULL(B.TASK_STATUS, 1) as TASK_STATUS,
			IFNULL(S.TASK_STATUS_DESC, 'INACTIVE') as TASK_STATUS_DESC,
			T.CMD_PARAMETERS,
			B.CMD_PARAMETERS as BATCH_CMD_PARAMETERS,
			T.TASK_DESC,
			T.TASK_ID, 
			TS.SCHEDULE_ID,
			G.TASK_GRP_ID,
			G.PARENT,
			G.TASK_GRP_NAME,
			G.TASK_GRP_DESC,
			B.LOG_MSG,
			B.START_TIME,
			B.END_TIME,
			B.DURATION,
			B.NEXT_RUN_TIME,
			B.EXIT_CODE,
			T.NO_OF_RETRY,
			T.SLEEP_BETWEEN_RETRY,
			T.[TIMEOUT],
			B.RETRY_COUNT,
			T.MACHINE_NAME,
			B.MACHINE_NAME as BATCH_MACHINE_NAME,
			B.MODIFIED_BY,
			IFNULL(B.MODIFIED_DATE, '1/1/1900') as MODIFIED_DATE,
			T.CREATED_DATE as TASK_CREATED_DATE,
			T.CREATED_BY as TASK_CREATED_BY,
			T.MODIFIED_DATE as TASK_MODIFIED_DATE,
			T.MODIFIED_BY as TASK_MODIFIED_BY,
			G.CREATED_DATE as TASK_GRP_CREATED_DATE,
			G.CREATED_BY as TASK_GRP_CREATED_BY,
			G.MODIFIED_DATE as TASK_GRP_MODIFIED_DATE,
			G.MODIFIED_BY as TASK_GRP_MODIFIED_BY
		FROM
			{1} T
			LEFT OUTER JOIN {0} G ON T.TASK_GRP_ID = G.TASK_GRP_ID
			LEFT OUTER JOIN {2} TS ON TS.TASK_ID = T.TASK_ID
			LEFT OUTER JOIN {3} B ON T.TASK_ID = B.TASK_ID AND TS.SCHEDULE_ID = B.SCHEDULE_ID
			LEFT OUTER JOIN {4} S ON B.TASK_STATUS = S.TASK_STATUS_ID
		WHERE 
			T.TASK_NAME = @TASK_NAME
			AND G.TASK_GRP_NAME = @TASK_GRP_NAME
            AND (B.MACHINE_NAME IS NULL OR B.MACHINE_NAME LIKE IFNULL(@MACHINE_NAME, '%'))
			AND (B.MODIFIED_DATE IS NOT NULL AND B.MODIFIED_DATE >= @MODIFIED_DATE)".FormatString(TASK_GRPS_TABLE_NAME, TASKS_TABLE_NAME, TASK_SCHEDULES_TABLE_NAME, TASK_BATCH_TABLE_NAME, TASK_STATUS_TABLE_NAME, ChoDbDateTime.MinValue.ToString("MM/dd/yyyy"));

        public static readonly string GET_TASK_SCHEDULE_ID = @"SELECT 
                SCHEDULE_ID 
            FROM 
                {0} 
            WHERE 
                SCHEDULE_NAME = @SCHEDULE_NAME AND TASK_ID = @TASK_ID".FormatString(TASK_SCHEDULES_TABLE_NAME);
        public static readonly string INSERT_TASK_SCHEDULE = @"INSERT INTO {0} (
            SCHEDULE_ID, SCHEDULE_NAME, SCHEDULE_DESC, TASK_ID, [ENABLED], [START_DATE], [END_DATE], 
			START_TIME, END_TIME, HOLIDAY_CAL_ID, SCHEDULE_TYPE_ID, T1, DAILY_EVERY_DAYS, WEEKLY_EVERY_WEEKS, 
			SUN, MON, TUE, WED, THU, FRI, SAT, MONTHLY_DAY_OF_MONTH,
			MONTHLY_EVERY_DAY_OF_MONTH, 
			MONTHLY_EVERY_WEEK_OF_MONTH,
			MONTHLY_EVERY_WEEKDAY_OF_MONTH,
			MONTHLY_SELECTED_MONTHS,
			REPEAT_ENABLED,
			REPEAT_INTERVAL,
			REPEAT_INTERVAL_TYPE,
			DEPENDENCIES,
			FIRE_AFTER_DEPENDENCY,
			CREATED_BY, CREATED_DATE,
			MODIFIED_BY, MODIFIED_DATE)
		VALUES (@SCHEDULE_ID, @SCHEDULE_NAME, @SCHEDULE_DESC, @TASK_ID, @ENABLED, @START_DATE, @END_DATE, 
			@START_TIME, @END_TIME, @HOLIDAY_CAL_ID, @SCHEDULE_TYPE_ID, @T1, @DAILY_EVERY_DAYS, @WEEKLY_EVERY_WEEKS, 
			@SUN, @MON, @TUE, @WED, @THU, @FRI, @SAT, @MONTHLY_DAY_OF_MONTH,
			@MONTHLY_EVERY_DAY_OF_MONTH, 
			@MONTHLY_EVERY_WEEK_OF_MONTH,
			@MONTHLY_EVERY_WEEKDAY_OF_MONTH,
			@MONTHLY_SELECTED_MONTHS,
			@REPEAT_ENABLED,
			@REPEAT_INTERVAL,
			@REPEAT_INTERVAL_TYPE,
			@DEPENDENCIES,
			@FIRE_AFTER_DEPENDENCY,
			@MODIFIED_BY, @MODIFIED_DATE,
			@MODIFIED_BY, @MODIFIED_DATE)".FormatString(TASK_SCHEDULES_TABLE_NAME);
        public static readonly string UPDATE_TASK_SCHEDULE = @"UPDATE {0} 
            SET
                SCHEDULE_DESC = @SCHEDULE_DESC, 
                ENABLED = @ENABLED, 
                START_DATE = @START_DATE, 
                END_DATE = @END_DATE,
			    START_TIME = @START_TIME, 
                END_TIME = @END_TIME, 
                HOLIDAY_CAL_ID = @HOLIDAY_CAL_ID, 
                SCHEDULE_TYPE_ID = @SCHEDULE_TYPE_ID, 
                T1 = @T1, 
                DAILY_EVERY_DAYS = @DAILY_EVERY_DAYS, 
                WEEKLY_EVERY_WEEKS = @WEEKLY_EVERY_WEEKS, 
			    SUN = @SUN, 
                MON = @MON, 
                TUE = @TUE, 
                WED = @WED, 
                THU = @THU, 
                FRI = @FRI, 
                SAT = @SAT, 
                MONTHLY_DAY_OF_MONTH = @MONTHLY_DAY_OF_MONTH,
			    MONTHLY_EVERY_DAY_OF_MONTH = @MONTHLY_EVERY_DAY_OF_MONTH, 
			    MONTHLY_EVERY_WEEK_OF_MONTH = @MONTHLY_EVERY_WEEK_OF_MONTH,
			    MONTHLY_EVERY_WEEKDAY_OF_MONTH = @MONTHLY_EVERY_WEEKDAY_OF_MONTH,
			    MONTHLY_SELECTED_MONTHS = @MONTHLY_SELECTED_MONTHS,
			    REPEAT_ENABLED = @REPEAT_ENABLED,
			    REPEAT_ENABLED = @REPEAT_ENABLED,
			    REPEAT_INTERVAL_TYPE = @REPEAT_INTERVAL_TYPE,
			    DEPENDENCIES = @DEPENDENCIES,
			    FIRE_AFTER_DEPENDENCY = @FIRE_AFTER_DEPENDENCY,
			    MODIFIED_BY = @MODIFIED_BY, 
                MODIFIED_DATE = @MODIFIED_DATE
            WHERE
                SCHEDULE_ID = @SCHEDULE_ID
                AND SCHEDULE_NAME = @SCHEDULE_NAME
                AND TASK_ID = @TASK_ID".FormatString(TASK_SCHEDULES_TABLE_NAME);
        public static readonly string DELETE_TASK_SCHEDULE = @"DELETE FROM {0} WHERE SCHEDULE_ID = @SCHEDULE_ID".FormatString(TASK_SCHEDULES_TABLE_NAME);

        public static readonly string GET_TASK_SCHEDULES = @"SELECT
			SCHEDULE_ID,
			SCHEDULE_NAME,
			SCHEDULE_DESC,
			TASK_ID,
			[ENABLED],
			[START_DATE],
			[END_DATE],
			START_TIME,
			END_TIME,
			HOLIDAY_CAL_ID,
			C.CAL_NAME as HOLIDAY_CAL_NAME,
			SCHEDULE_TYPE_ID,
			T1,
			DAILY_EVERY_DAYS,
			WEEKLY_EVERY_WEEKS,
			SUN,
			MON,
			TUE,
			WED,
			THU,
			FRI,
			SAT,
			MONTHLY_DAY_OF_MONTH,
			MONTHLY_EVERY_DAY_OF_MONTH,
			MONTHLY_EVERY_WEEK_OF_MONTH,
			MONTHLY_EVERY_WEEKDAY_OF_MONTH,
			MONTHLY_SELECTED_MONTHS,
			REPEAT_ENABLED,
			REPEAT_INTERVAL,
			REPEAT_INTERVAL_TYPE,
			DEPENDENCIES,
			FIRE_AFTER_DEPENDENCY,
			T.CREATED_BY, T.CREATED_DATE, T.MODIFIED_BY, T.MODIFIED_DATE
		FROM 
			{0} T
			LEFT OUTER JOIN {1} C ON C.CAL_ID = T.HOLIDAY_CAL_ID
		WHERE 
			TASK_ID = @TASK_ID
            AND SCHEDULE_NAME LIKE IFNULL(@SCHEDULE_NAME, '%')".FormatString(TASK_SCHEDULES_TABLE_NAME, HOLIDAY_CALENDAR_TABLE_NAME);

        public static readonly string GET_ALL_DEPENDENCY_TASKS = @"SELECT
		    T.TASK_ID,
		    T.TASK_NAME,
		    T.WORKFLOW_NAME,
		    TS.SCHEDULE_ID,
		    TS.SCHEDULE_NAME,
		    TS.DEPENDENCIES,
		    TS.FIRE_AFTER_DEPENDENCY,
		    B.TASK_STATUS,
		    B.START_TIME
	    FROM
		    {0} T
		    LEFT OUTER JOIN {1} B ON T.TASK_ID = B.TASK_ID 
			    -- AND B.[VERSION] = (SELECT MAX(X.[VERSION]) FROM {1} X WHERE X.TASK_ID = T.TASK_ID)
		    LEFT OUTER JOIN {2} TS ON T.TASK_ID = TS.TASK_ID
	    WHERE 
		    TS.DEPENDENCIES LIKE @JOB_NAME".FormatString(TASKS_TABLE_NAME, TASK_BATCH_TABLE_NAME, TASK_SCHEDULES_TABLE_NAME);

        public static readonly string IS_VALID_TASK_STATUS = @"SELECT 1 FROM {0} WHERE TASK_STATUS_ID = @TASK_STATUS_ID".FormatString(TASK_STATUS_TABLE_NAME);
        public static readonly string IS_TASK_BATCH_EXISTS = @"SELECT 1 FROM {0} WHERE TASK_ID = @TASK_ID AND SCHEDULE_ID = @SCHEDULE_ID".FormatString(TASK_BATCH_TABLE_NAME);

        public static readonly string INSERT_TASK_BATCH = @"INSERT INTO {0} (BATCH_ID, TASK_ID, SCHEDULE_ID, TASK_STATUS, 
                CMD_PARAMETERS, MACHINE_NAME, LOG_MSG, START_TIME, END_TIME, DURATION, NEXT_RUN_TIME, EXIT_CODE, RETRY_COUNT, MODIFIED_BY, MODIFIED_DATE)
		    VALUES (@BATCH_ID, @TASK_ID, @SCHEDULE_ID, @TASK_STATUS, @CMD_PARAMETERS, @MACHINE_NAME, @LOG_MSG, @START_TIME, @END_TIME, @DURATION, 
                @NEXT_RUN_TIME, @EXIT_CODE, @RETRY_COUNT, @MODIFIED_BY, @MODIFIED_DATE)".FormatString(TASK_BATCH_TABLE_NAME);
        public static readonly string UPDATE_TASK_BATCH = @"UPDATE {0}
			SET 
                BATCH_ID = @BATCH_ID,
				TASK_STATUS = @TASK_STATUS,
				CMD_PARAMETERS = @CMD_PARAMETERS,
				MACHINE_NAME = @MACHINE_NAME,
				LOG_MSG = @LOG_MSG,
				START_TIME = @START_TIME,
				END_TIME = @END_TIME,
				DURATION = @DURATION,
				NEXT_RUN_TIME = @NEXT_RUN_TIME,
				EXIT_CODE = @EXIT_CODE,
				RETRY_COUNT = @RETRY_COUNT,
				MODIFIED_BY = @MODIFIED_BY,
				MODIFIED_DATE = @MODIFIED_DATE
			WHERE
				SCHEDULE_ID = @SCHEDULE_ID".FormatString(TASK_BATCH_TABLE_NAME);
        public static readonly string DELETE_TASK_BATCH = @"DELETE FROM {0}
			WHERE
				SCHEDULE_ID = @SCHEDULE_ID".FormatString(TASK_BATCH_TABLE_NAME);

        public static readonly string IS_TASK_GROUP_EXISTS = @"SELECT 1 FROM {0} WHERE TASK_GRP_NAME = @TASK_GRP_NAME".FormatString(TASK_GRPS_TABLE_NAME);
        public static readonly string INSERT_TASK_GROUP = @"INSERT INTO {0} (TASK_GRP_ID, TASK_GRP_NAME, TASK_GRP_DESC, PARENT,
                    CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)
                VALUES (@TASK_GRP_ID, @TASK_GRP_NAME, @TASK_GRP_DESC, @PARENT, @MODIFIED_BY, @MODIFIED_DATE, @MODIFIED_BY, @MODIFIED_DATE)".FormatString(TASK_GRPS_TABLE_NAME);
        public static readonly string UPDATE_TASK_GROUP = @"UPDATE {0}
				SET 
                    TASK_GRP_NAME = @TASK_GRP_NAME,
                    TASK_GRP_DESC = @TASK_GRP_DESC,
                    PARENT = @PARENT,
					MODIFIED_BY = @MODIFIED_BY,
					MODIFIED_DATE = @MODIFIED_DATE
				WHERE
                    TASK_GRP_ID = @TASK_GRP_ID".FormatString(TASK_GRPS_TABLE_NAME);
        public static readonly string DELETE_TASK_GROUP = @"DELETE FROM  
                {0} 
            WHERE 
                TASK_GRP_NAME = @TASK_GRP_NAME".FormatString(TASK_GRPS_TABLE_NAME);

        public static readonly string GET_ALL_HOLIDAY_CAL = @"SELECT CAL_ID, CAL_NAME, CAL_DESC, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE FROM {0}".FormatString(HOLIDAY_CALENDAR_TABLE_NAME);
        public static readonly string GET_HOLIDAY_CAL_ID = @"SELECT CAL_ID FROM {0} WHERE CAL_NAME = @CAL_NAME".FormatString(HOLIDAY_CALENDAR_TABLE_NAME);
        public static readonly string GET_HOLIDAY_CAL_NAME = @"SELECT CAL_NAME FROM {0} WHERE CAL_ID = @CAL_ID".FormatString(HOLIDAY_CALENDAR_TABLE_NAME);
        public static readonly string INSERT_HOLIDAY_CAL = @"INSERT INTO {0} (CAL_ID, CAL_NAME, CAL_DESC, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)
            VALUES (@CAL_ID, @CAL_NAME, @CAL_DESC, @MODIFIED_BY, @MODIFIED_DATE, @MODIFIED_BY, @MODIFIED_DATE)".FormatString(HOLIDAY_CALENDAR_TABLE_NAME);
        public static readonly string UPDATE_HOLIDAY_CAL = @"UPDATE {0}
			SET 
                CAL_DESC = @CAL_DESC,
				MODIFIED_BY = @MODIFIED_BY,
				MODIFIED_DATE = @MODIFIED_DATE
			WHERE
				CAL_NAME = @CAL_NAME AND CAL_ID = @CAL_ID".FormatString(HOLIDAY_CALENDAR_TABLE_NAME);
        public static readonly string DELETE_HOLIDAY_CAL = @"DELETE FROM {0} WHERE CAL_NAME = @CAL_NAME".FormatString(HOLIDAY_CALENDAR_TABLE_NAME);

        public static readonly string GET_HOLIDAY_CAL_DATES_BY_NAME = @"SELECT 
		    T1.CAL_ID, 
		    T1.CAL_DATE, 
		    T1.CAL_DATE_DESC,
		    T1.CREATED_BY,
		    T1.CREATED_DATE,
		    T1.MODIFIED_BY,
		    T1.MODIFIED_DATE
	    FROM 
		    {0} T1, 
		    {1} T2 
	    WHERE 
		    T2.CAL_NAME = @CAL_NAME
		    AND T1.CAL_ID = T2.CAL_ID".FormatString(HOLIDAY_CALENDAR_DATES_TABLE_NAME, HOLIDAY_CALENDAR_TABLE_NAME);
        public static readonly string GET_HOLIDAY_CAL_DATES_BY_ID = @"SELECT 
            CAL_ID, CAL_DATE, CAL_DATE_DESC, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE FROM {0} WHERE CAL_ID = @CAL_ID".FormatString(HOLIDAY_CALENDAR_DATES_TABLE_NAME);
        public static readonly string IS_HOLIDAY_CAL_DATE_EXISTS = @"SELECT 1 FROM {0} WHERE CAL_ID = @CAL_ID AND CAL_DATE = @CAL_DATE".FormatString(HOLIDAY_CALENDAR_DATES_TABLE_NAME);
        public static readonly string INSERT_HOLIDAY_CAL_DATE = @"INSERT INTO {0} (CAL_ID, CAL_DATE, CAL_DATE_DESC, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)
			VALUES (@CAL_ID, @CAL_DATE, @CAL_DATE_DESC, @MODIFIED_BY, @MODIFIED_DATE, @MODIFIED_BY, @MODIFIED_DATE)".FormatString(HOLIDAY_CALENDAR_DATES_TABLE_NAME);
        public static readonly string UPDATE_HOLIDAY_CAL_DATE = @"UPDATE {0}
			SET 
				CAL_DATE_DESC = @CAL_DATE_DESC,
				MODIFIED_BY = @MODIFIED_BY,
				MODIFIED_DATE = @MODIFIED_DATE
			WHERE
				CAL_ID = @CAL_ID AND CAL_DATE = @CAL_DATE".FormatString(HOLIDAY_CALENDAR_DATES_TABLE_NAME);
        public static readonly string DELETE_HOLIDAY_CAL_DATE = @"DELETE FROM {0} WHERE CAL_ID = @CAL_ID AND CAL_DATE = @CAL_DATE".FormatString(HOLIDAY_CALENDAR_DATES_TABLE_NAME);

        public static readonly string GET_POLICY_FOR_USER = @"SELECT 
		        P.POLICY_ID, P.POLICY_DESC
	        FROM
		        {0} U,
		        {1} UR,
		        {2} RP,
		        {3} P,
		        {4} R
	        WHERE
		        U.NAME = @USER_NAME
		        AND U.ID = UR.USER_ID
		        AND UR.ROLE_ID = RP.ROLE_ID
		        AND RP.POLICY_ID = P.POLICY_ID
		        AND UR.ROLE_ID = R.ID
		        AND R.ENABLED = 1
		        AND U.ENABLED = 1".FormatString(USERS_TABLE_NAME, USER_ROLES_TABLE_NAME, ROLE_POLICY_TABLE_NAME, POLICY_TABLE_NAME, ROLES_TABLE_NAME);

        public static readonly string GET_ALL_SCHEDULE_TYPES = @"SELECT TASK_SCHEDULE_TYPE_ID, TASK_SCHEDULE_TYPE_DESC FROM {0}".FormatString(SCHEDULE_TYPES_TABLE_NAME);
        public static readonly string GET_ALL_POLICY = @"SELECT
		    POLICY_ID,
		    POLICY_DESC
	    FROM 
		    {0}".FormatString(POLICY_TABLE_NAME);
        public static readonly string GET_ALL_USERS = @"SELECT ID, NAME, FULL_NAME, PASSWORD, ENABLED FROM {0} ORDER BY NAME".FormatString(USERS_TABLE_NAME);

        public static readonly string GET_USER_ID = @"SELECT ID FROM {0} WHERE NAME = @NAME".FormatString(USERS_TABLE_NAME);
        public static readonly string INSERT_USER = @"INSERT INTO {0} (ID, NAME, FULL_NAME, PASSWORD, ENABLED)
			VALUES (@ID, @NAME, @FULL_NAME, @PASSWORD, @ENABLED)".FormatString(USERS_TABLE_NAME);
        public static readonly string UPDATE_USER = @"UPDATE {0}
		    SET
			    FULL_NAME = @FULL_NAME,
			    PASSWORD = @PASSWORD,
			    ENABLED = @ENABLED
		    WHERE
                ID = @ID
			    AND NAME = @NAME".FormatString(USERS_TABLE_NAME);
        public static readonly string DELETE_USER = @"DELETE FROM {0}
		    WHERE
			    ID = @ID".FormatString(USERS_TABLE_NAME);

        public static readonly string GET_ALL_ROLES = @"SELECT 
		    R.ID, 
		    R.NAME,
		    R.COMMENT,
		    R.ENABLED
	    FROM
		    {0} R
	    ORDER BY
		    R.NAME".FormatString(ROLES_TABLE_NAME);
        public static readonly string GET_ROLE_ID = @"SELECT ID FROM {0} WHERE NAME = @NAME".FormatString(ROLES_TABLE_NAME);
        public static readonly string INSERT_ROLE = @"INSERT INTO {0} (ID, NAME, COMMENT, ENABLED)
			VALUES (@ID, @NAME, @COMMENT, @ENABLED)".FormatString(ROLES_TABLE_NAME);
        public static readonly string UPDATE_ROLE = @"UPDATE {0}
		SET 
			COMMENT = @COMMENT,
			ENABLED = @ENABLED
		WHERE
            ID = @ID
			AND NAME = @NAME".FormatString(ROLES_TABLE_NAME);
        public static readonly string DELETE_USER_ROLES_FOR_ROLE = @"DELETE FROM {0} WHERE ROLE_ID = @ID".FormatString(USER_ROLES_TABLE_NAME);
        public static readonly string DELETE_ROLE_POLICY_FOR_ROLE = @"DELETE FROM {0} WHERE ROLE_ID = @ID".FormatString(ROLE_POLICY_TABLE_NAME);
        public static readonly string DELETE_ROLE = @"DELETE FROM {0} WHERE ID = @ID".FormatString(ROLES_TABLE_NAME);

        public static readonly string DELETE_USER_ROLE = @"DELETE FROM {0} WHERE USER_ID = @USER_ID".FormatString(USER_ROLES_TABLE_NAME);
        public static readonly string DELETE_USER_ROLE_FOR_USER_ROLE = @"DELETE FROM {0} WHERE USER_ID = @USER_ID AND ROLE_ID = @ROLE_ID".FormatString(USER_ROLES_TABLE_NAME);
        public static readonly string INSERT_USER_ROLE = @"INSERT INTO {0} (USER_ID, ROLE_ID)
			VALUES (@USER_ID, @ROLE_ID)".FormatString(USER_ROLES_TABLE_NAME);

        public static readonly string GET_ALL_USER_ROLE = @"SELECT 
		    R.ID, 
		    R.NAME, 
		    UR.USER_ID,
		    U.NAME as USER_NAME,
		    U.FULL_NAME as USER_FULL_NAME
	    FROM
		    {0} R
		    JOIN {1} UR ON R.ID = UR.ROLE_ID
		    JOIN {2} U ON UR.USER_ID = U.ID
	    WHERE
		    R.ID = @ROLE_ID
	    ORDER BY
		    U.FULL_NAME".FormatString(ROLES_TABLE_NAME, USER_ROLES_TABLE_NAME, USERS_TABLE_NAME);

        public static readonly string GET_ALL_ROLE_POLICY = @"SELECT 
		    R.ID, 
		    R.NAME, 
		    RP.POLICY_ID,
		    P.POLICY_DESC
	    FROM
		    {0} R
		    JOIN {1} RP ON R.ID = RP.ROLE_ID
		    JOIN {2} P ON RP.POLICY_ID = P.POLICY_ID
	    WHERE
		    R.ID = @ROLE_ID".FormatString(ROLES_TABLE_NAME, ROLE_POLICY_TABLE_NAME, POLICY_TABLE_NAME);
        public static readonly string IS_ROLE_POLICY_EXISTS = @"SELECT 1 FROM {0} WHERE ROLE_ID = @ROLE_ID AND POLICY_ID = @POLICY_ID".FormatString(ROLE_POLICY_TABLE_NAME);
        public static readonly string INSERT_ROLE_POLICY = @"INSERT INTO {0} (ROLE_ID, POLICY_ID)
			VALUES (@ROLE_ID, @POLICY_ID)".FormatString(ROLE_POLICY_TABLE_NAME);
        public static readonly string DELETE_ROLE_POLICY = @"DELETE FROM {0} 
		    WHERE 
			    ROLE_ID = @ROLE_ID
			    AND POLICY_ID = @POLICY_ID".FormatString(ROLE_POLICY_TABLE_NAME);
    }
}
